package com.swadar.dao;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.swadar.beans.dataobject.BrandDO;
import com.swadar.beans.dataobject.CategoryDO;
import com.swadar.beans.valueobject.ProductVO;
import com.swadar.beans.valueobject.RecProductVO;
import com.swadar.beans.valueobject.UserVO;





@Component
public class CommonDAO {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	private static final String PRODUCT_LIST = "SELECT *  FROM PRODUCTS WHERE IS_BARWARE = ?";
	private static final String CATEGORY_LIST = "SELECT * FROM CATEGORIES";
	private static final String GET_PRODUCT = "SELECT P.ID,P.NAME,P.DESCRIPTION,P.IMAGE,P.QUANTITY,P.PRICE,P.ML FROM PRODUCTS P WHERE P.ID = ?";
	private static final String GET_REC_PRODUCT = "SELECT * FROM RECPRODUCTS WHERE PRODUCT_ID = ?";
	private static final String GET_PRODUCT_LIST = "SELECT P.ID,P.NAME,P.DESCRIPTION,P.IMAGE,P.PRICE,P.MAPPING_ID FROM PRODUCTS P JOIN BRAND_CATEGORY_MAPPING BM ON P.MAPPING_ID = BM.ID WHERE BM.CATEGORY_ID = ? AND BM.BRAND_ID = ?";
	private static final String GET_IMAGE = "SELECT * FROM BRAND";
	private static final String GET_IMAGE_PDODUCT_LIST = "SELECT P.ID,P.NAME,P.DESCRIPTION,P.IMAGE,P.PRICE,P.QUANTITY,P.MAPPING_ID FROM PRODUCTS P JOIN BRAND_CATEGORY_MAPPING BM ON P.MAPPING_ID = BM.ID WHERE BM.BRAND_ID = ?";
	
	public List<ProductVO> getProductList(boolean isBarware) {
		Integer value = isBarware ? 1 : 0;
		Object[] params = { value };
		RowMapper<ProductVO> mapper = new BeanPropertyRowMapper<ProductVO>(ProductVO.class);
		List<ProductVO> productVOList = jdbcTemplate.query(PRODUCT_LIST, params, mapper);
		return productVOList;
	}
	
	public ProductVO getProductDetails(Integer productId){
		BeanPropertyRowMapper<ProductVO> rowMapper = new BeanPropertyRowMapper<ProductVO>(ProductVO.class);
		Object[] params = {productId};
		ProductVO productVO = jdbcTemplate.queryForObject(GET_PRODUCT,params,rowMapper);
		return productVO;
	}
	
	public List<RecProductVO> getRecProduct(Integer productId){
		BeanPropertyRowMapper<RecProductVO> rowMapper = new BeanPropertyRowMapper<RecProductVO>(RecProductVO.class);
		Object[] param = { productId };
		List<RecProductVO> recProductList = jdbcTemplate.query(GET_REC_PRODUCT, param, rowMapper);
		return recProductList;
		
	}
	
   public Integer isValidUser(UserVO userVo){
		
		String sql = "SELECT COUNT(*) FROM users u JOIN user_login ul on u.id=ul.user_id and u.EMAIL=? AND ul.PASSWORD=?";
		Object[] params = { userVo.geteMail(), userVo.getPassword() };
		Integer count = jdbcTemplate.queryForObject(sql,Integer.class,params);
		return count;
	}
   
   public List<CategoryDO> getCategoryList(){
	    BeanPropertyRowMapper<CategoryDO> rowMapper = new BeanPropertyRowMapper<CategoryDO>(CategoryDO.class);
	    List<CategoryDO> categoryList = jdbcTemplate.query(CATEGORY_LIST, rowMapper);
	    return categoryList;
   }
   
   public List<ProductVO> getProductList(Long categoryId, Long brandId){
	   BeanPropertyRowMapper<ProductVO> rowMapper = new BeanPropertyRowMapper<ProductVO>(ProductVO.class);
	   List<ProductVO> productList = null;
	   if(categoryId != null && brandId != null){
		   Object[] param = { categoryId, brandId };
		   productList = jdbcTemplate.query(GET_PRODUCT_LIST, param, rowMapper);
	   }
	   else if(brandId != null && categoryId == null){
		   Object[] param = { brandId };
		   productList = jdbcTemplate.query(GET_IMAGE_PDODUCT_LIST, param, rowMapper);
	   }
	   return productList;
   }
   
   public List<BrandDO> getBrandList(Long categoryId){
	   Object[] param = { categoryId };
	   BeanPropertyRowMapper<BrandDO> rowMapper = new BeanPropertyRowMapper<BrandDO>(BrandDO.class);
	   List<BrandDO> brandList = jdbcTemplate.query("GET_BRAND", param, rowMapper);
	   return brandList;
	   
   }

   public List<BrandDO> getBrandImage(){
	   BeanPropertyRowMapper<BrandDO> rowMapper = new BeanPropertyRowMapper<BrandDO>(BrandDO.class);
	   List<BrandDO> imageList = jdbcTemplate.query(GET_IMAGE, rowMapper);
	   return imageList;
   }
   
}
